<html>
<head>
    <meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>Hadoop到Mysql的数据传输记录 | |ω･`)暗中观察</title>
<meta name="description" content="大爷不进来看看吗？这里有好康的哦~~~" />
<link rel="shortcut icon" href="https://Yanoona.github.io/favicon.ico">
<link rel="stylesheet" href="https://Yanoona.github.io/styles/main.css">

<script src="https://Yanoona.github.io/media/js/jquery.min.js"></script>
<script src="https://Yanoona.github.io/media/js/masonry.pkgd.min.js"></script>
<script src="https://Yanoona.github.io/media/js/aos.js"></script>
<script src="https://Yanoona.github.io/media/js/pace.min.js"></script>
<script src="https://Yanoona.github.io/media/js/view-image.min.js"></script>
<script src="https://Yanoona.github.io/media/js/functions.js"></script>
    <script src="https://Yanoona.github.io/media/js/waterfall.min.js"></script>
    <script src="https://Yanoona.github.io/media/js/prism.min.js"></script>
</head>
<body>
<header id="header" class="grid-container">
    <!-- start: .menu-wrapper -->
    <div class="menu-mobile">
        <i class="fa fa-reorder"></i>
    </div>
    <div class="menu-wrapper">
        <div class="">
            <nav class="main-nav " >
                <ul id="menu-header" class="menu gradient-effect">
                    
                        <li class="">
                            <a href="https://Yanoona.github.io" class="menu">
                                首页
                            </a>
                        </li>
                    
                        <li class="">
                            <a href="https://Yanoona.github.io/archives" class="menu">
                                归档
                            </a>
                        </li>
                    
                        <li class="">
                            <a href="https://Yanoona.github.io/tags" class="menu">
                                标签
                            </a>
                        </li>
                    
                        <li class="">
                            <a href="https://github.com/Yanoona" class="menu">
                                关于
                            </a>
                        </li>
                    
                </ul>
            </nav>
            <!-- end: .main-nav -->
            <div class="clear"></div>
            <div class="border hide-on-tablet hide-on-mobile"></div>
        </div>
        <div class="clear"></div>
    </div>
    <!-- end: .menu-wrapper -->
    <div class="clear"></div>
</header>

<main id="single" class="main grid-container fullcover no-sidebar aos-init aos-animate" data-aos="fade">

    <div class="center content">
        <div class="featured-image cover"
             style="background-image: url('https://Yanoona.github.io/post-images/NNC7-_gM_.jpg');">
            <div class="meta top">
                <time class="meta-info" style="float:left;" datetime="2020-02-19"><i
                            class="fa fa-calendar"></i><span
                            class="lately">4 个月前</span></time>
            </div>
            <div class="info">
                <div class="tags ">
                    
                        <a href="https://Yanoona.github.io/Hadoop/" class="ctag ctag-0 ctag-Hadoop"
                           aria-label="">Hadoop</a>
                    
                        <a href="https://Yanoona.github.io/Sqoop/" class="ctag ctag-1 ctag-Sqoop"
                           aria-label="">Sqoop</a>
                    
                        <a href="https://Yanoona.github.io/Hive/" class="ctag ctag-2 ctag-Hive"
                           aria-label="">Hive</a>
                    
                        <a href="https://Yanoona.github.io/mysql/" class="ctag ctag-3 ctag-mysql"
                           aria-label="">mysql</a>
                    
                </div>
                <h1 class="title ularge white bold">Hadoop到Mysql的数据传输记录</h1>
            </div>
        </div>
    </div>

    <div class="epcl-page-wrapper">
        <div class="left-content grid-70 np-mobile">
            <article class="main-article post">
                <section class="post-content">
                    <div class="text">
                        <h2 id="概述">概述</h2>
<p>本文档主要记录使用 <code>HIve Load</code> 将 <code>Hadoop</code> 中的文件保存到 <code>HIve</code> 的数据库中，再将其通过 <code>Sqoop export</code> 到 <code>Mysql</code> 的详细步骤🙊；</p>
<!-- more -->
<h2 id="环境及测试数据说明">环境及测试数据说明</h2>
<ul>
<li>Hadoop - 2.9.2</li>
<li>CentOS 7.9</li>
<li>Hive 2.3.9</li>
<li>Mysql 8.0</li>
<li>Sqoop 1.4.7</li>
<li>zy_fy_detail        659MB          5,691,514行</li>
<li>zy_fy_year          512KB           4,656行</li>
<li>zy_fy_yearmonth           59.59MB           516,883行</li>
<li>以上表的文本间隔符为 <code>\001</code></li>
</ul>
<h2 id="开始">开始</h2>
<h3 id="确认测试环境">确认测试环境</h3>
<ul>
<li>🌕使用<code>jps</code>查看 Hadoop 集群是否正常启动（包括<code>jobhistory</code>）</li>
<li>🌖使用<code>systemctl status mysqld</code>查看<code>Mysql</code>是否正常运行</li>
<li>🌗确认<code>hive</code>已经配置好了<code>mysql</code></li>
</ul>
<hr>
<h3 id="将测试数据上传到hadoop中">将测试数据上传到Hadoop中</h3>
<blockquote>
<p>上传文件可以自己搭建ftp服务器实现，或者使用hadoop中的网页上传功能。👏👏</p>
</blockquote>
<p><strong>此处使用Hadoop上传</strong>：</p>
<p>​		在浏览器除输入 http://hadoop1:50070:/，<em>hadoop1</em>为我这里的<em>NameNode</em>节点的主机名（如访问不了，可以试下换成IP访问😁）。点击 <em>Browse the file system</em> 进入目录页并单击红框处上传按钮。</p>
<figure data-type="image" tabindex="1"><img src="https://Yanoona.github.io/post-images/1582092792811.png" alt="" loading="lazy"></figure>
<p>如果出现以下类似信息🙄🙄（可以确定是<strong>权限问题</strong>）：</p>
<figure data-type="image" tabindex="2"><img src="https://Yanoona.github.io/post-images/1582092804261.png" alt="" loading="lazy"></figure>
<p>使用以下命令赋予权限后，就可以进行上传☝️、删除💀等操作：</p>
<pre><code>hadoop fs -chmod 777 /user/admin/*
</code></pre>
<hr>
<h3 id="创建接收数据的hive数据库">创建接收数据的Hive数据库</h3>
<p><strong>😋进入Hive，并创建数据库（此处示例数据库名：bigdata）</strong></p>
<pre><code>#进入Hive
hive
#显示数据库
show databases;
#创建数据库
create database bigdata;
#选择数据库
use bigdata;
</code></pre>
<p><strong>🙁创建可识别多个分隔符的表（zy_fy_year）</strong></p>
<pre><code> CREATE TABLE `zy_fy_year` (
       `year` varchar(4),
       `SFXM` decimal(4,0),
       `FYMC` varchar(160),
       `FYDJ` decimal(10,4),
       `year_sl` decimal(32,2),
       `year_ZJJE` decimal(34,2)
     )  row format  serde 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' with  serdeproperties (&quot;field.delim&quot;=&quot;&amp;*#@&quot;);

</code></pre>
<blockquote>
<p>Hive在0.14及以后版本支持字段的多分隔符，参考🚀<a href="https://link.jianshu.com/?t=https%3A%2F%2Fcwiki.apache.org%2Fconfluence%2Fdisplay%2FHive%2FMultiDelimitSerDe">https://cwiki.apache.org/confluence/display/Hive/MultiDelimitSerDe</a>🚀</p>
<p>关键语句为👉：<code>row format serde 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' with serdeproperties (&quot;field.delim&quot;=&quot;你的分隔符&quot;);</code></p>
<p>当只有一个分隔符时👉：<code>row format delimited fields terminated by '分隔符'</code></p>
</blockquote>
<p>😎<strong>使用Hive Load加载HDFS文件到表中</strong></p>
<pre><code>#此处地址为Hadoop中zy_fy_year.txt文件的地址
load data inpath '/test_data/test_files/zy_fy_year.txt' overwirte into table bigdata.zy_fy_year
#查看插入结果是否有错
select * from zy_fy_year limit 0,10;
</code></pre>
<figure data-type="image" tabindex="3"><img src="https://Yanoona.github.io/post-images/1582092825986.png" alt="" loading="lazy"></figure>
<hr>
<h3 id="使用sqoop将hive中的数据导出到mysql中">😉使用Sqoop将Hive中的数据导出到Mysql中</h3>
<p><strong>在Mysql创建导出数据库（bigdata）和表（zy_fy_year）🤗</strong></p>
<pre><code>#登录mysql
mysql -u root -p
#创建数据库
create database bigdata;
#选择数据库
use bigdata;
#创建表
create table `zy_fy_year` (
  `year` varchar(4) CHARACTER SET utf8mb4 DEFAULT NULL,
  `SFXM` decimal(4,0) NOT NULL COMMENT '收费项目',
  `FYMC` varchar(160) DEFAULT NULL COMMENT '费用名称',
  `FYDJ` decimal(10,4) NOT NULL COMMENT '费用单价',
  `year_sl` decimal(32,2) DEFAULT NULL COMMENT '年汇总数量',
  `year_ZJJE` decimal(34,2) DEFAULT NULL COMMENT '总计金额'
) engine=InnoDB default charset=utf8;
</code></pre>

                    </div>
                    <div class="clear"></div>
                </section>
            </article>
            <div class="clear"></div>

            <section class="related section">
                
                    <article class="prev grid-50 tablet-grid-50 grid-parent">
                        <div class="thumb cover lazy loaded"
                             style="background-image: url('https://Yanoona.github.io/post-images/vqAakgQmf.jpg');"></div>
                        <a href="https://Yanoona.github.io/vqAakgQmf/" class="full-link"></a>
                        <div class="info">
                            <time datetime="2020-02-19">2020-02-19</time>
                            <h4 class="title white no-margin">Sqoop的安装和使用</h4>
                        </div>
                        <span class="epcl-button red">
                  <img src="https://Yanoona.github.io/media/images/left-arrow.svg" width="15" alt="Left Arrow">
                </span>
                        <div class="overlay"></div>
                    </article>
                
                
                    <article class="next grid-50 tablet-grid-50 grid-parent">
                        <div class="thumb cover lazy loaded"
                             style="background-image: url('https://Yanoona.github.io/post-images/TeSMMvZK8.jpg');"></div>
                        <a href="https://Yanoona.github.io/TeSMMvZK8/" class="full-link"></a>
                        <div class="info">
                            <time datetime="2020-02-19">2020-02-19</time>
                            <h4 class="title white no-margin">CentOS 7安装和卸载MySql</h4>
                        </div>
                        <span class="epcl-button red">
                  <img src="https://Yanoona.github.io/media/images/right-arrow.svg" width="15" alt="Left Arrow">
                </span>
                        <div class="overlay"></div>
                    </article>
                

                <div class="clear"></div>
            </section>
        </div>
    </div>
</main>

    <footer id="footer" class="grid-container">
        <div class="widgets row gradient-effect">
            <div class="default-sidebar border-effect">
              <div class="grid-33 tablet-grid-50 mobile-grid-100">
                <section id="tag_cloud-2" class="widget widget_epcl_posts_thumbs underline-effect">
                  <h4 class="widget-title title white bordered">最新文章</h4>
                  
                  
                  <article class="item post-0 post type-post status-publish format-standard has-post-thumbnail hentry">
                    <a href="https://Yanoona.github.io/2rO0MFRF0/" class="thumb hover-effect">
                      <span class="fullimage cover" style="display: block;background-image: url('https://Yanoona.github.io/post-images/2rO0MFRF0.jpg');"></span>
                    </a>
                    <div class="info gradient-effect">
                      <time datetime="2020-06-29">2020-06-29</time>
                      <h4 class="title usmall">
                        <a href="https://Yanoona.github.io/2rO0MFRF0/">Java8 Stream流学习笔记</a>
                      </h4>
                    </div>
                    <div class="clear"></div>
                  </article>
                  
                  
                  
                  <article class="item post-1 post type-post status-publish format-standard has-post-thumbnail hentry">
                    <a href="https://Yanoona.github.io/ta0wVkBzF/" class="thumb hover-effect">
                      <span class="fullimage cover" style="display: block;background-image: url('https://Yanoona.github.io/post-images/ta0wVkBzF.jpg');"></span>
                    </a>
                    <div class="info gradient-effect">
                      <time datetime="2020-05-13">2020-05-13</time>
                      <h4 class="title usmall">
                        <a href="https://Yanoona.github.io/ta0wVkBzF/">Spring 知识回顾之 体系结构</a>
                      </h4>
                    </div>
                    <div class="clear"></div>
                  </article>
                  
                  
                  
                  <article class="item post-2 post type-post status-publish format-standard has-post-thumbnail hentry">
                    <a href="https://Yanoona.github.io/CpjS5YWXn/" class="thumb hover-effect">
                      <span class="fullimage cover" style="display: block;background-image: url('https://Yanoona.github.io/post-images/CpjS5YWXn.jpg');"></span>
                    </a>
                    <div class="info gradient-effect">
                      <time datetime="2020-05-13">2020-05-13</time>
                      <h4 class="title usmall">
                        <a href="https://Yanoona.github.io/CpjS5YWXn/">Spring 知识回顾之 概念</a>
                      </h4>
                    </div>
                    <div class="clear"></div>
                  </article>
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  
                  <div class="clear"></div>
                </section>
              </div>

              <div class="grid-33 tablet-grid-50 mobile-grid-100">
                <section id="tag_cloud-2" class="widget widget_tag_cloud underline-effect">
                  <h4 class="widget-title title white bordered">标签云</h4>
                  <div class="tagcloud">
                    
                      <a href="https://Yanoona.github.io/java/" class="ctag ctag-0 ctag-java" aria-label="">Java</a>
                    
                      <a href="https://Yanoona.github.io/7D-9V5P5T/" class="ctag ctag-1 ctag-7D-9V5P5T" aria-label="">Spring</a>
                    
                      <a href="https://Yanoona.github.io/wDtcCNomO/" class="ctag ctag-2 ctag-wDtcCNomO" aria-label="">Crontab定时任务</a>
                    
                      <a href="https://Yanoona.github.io/j_dWapwnj/" class="ctag ctag-3 ctag-j_dWapwnj" aria-label="">CentOS 7</a>
                    
                      <a href="https://Yanoona.github.io/dXBZlTYs0/" class="ctag ctag-4 ctag-dXBZlTYs0" aria-label="">Vue</a>
                    
                      <a href="https://Yanoona.github.io/bigData/" class="ctag ctag-5 ctag-bigData" aria-label="">大数据</a>
                    
                      <a href="https://Yanoona.github.io/Sqoop/" class="ctag ctag-6 ctag-Sqoop" aria-label="">Sqoop</a>
                    
                      <a href="https://Yanoona.github.io/Hive/" class="ctag ctag-7 ctag-Hive" aria-label="">Hive</a>
                    
                      <a href="https://Yanoona.github.io/mysql/" class="ctag ctag-8 ctag-mysql" aria-label="">mysql</a>
                    
                      <a href="https://Yanoona.github.io/Hadoop/" class="ctag ctag-9 ctag-Hadoop" aria-label="">Hadoop</a>
                    
                  </div>
                  <div class="clear"></div>
                </section>
              </div>

              <div class="grid-33 tablet-grid-50 mobile-grid-100">
                <section id="epcl_about-2" class="widget widget_epcl_about underline-effect">
                  <h4 class="widget-title title white bordered">关于我</h4>
                  <div class="avatar">
                    <a href="https://github.com/Yanoona" class="translate-effect thumb"><span class="fullimage cover" style="background-image: url(https://Yanoona.github.io/images/avatar.png);"></span></a>
                  </div>
                  <div class="info">
                    <h4 class="title small author-name gradient-effect no-margin"><a href="https://github.com/Yanoona">|ω･`)暗中观察</a></h4>
                    <p class="founder">大爷不进来看看吗？这里有好康的哦~~~</p>
                    <div class="social">
                      
                        
                            <a href="https://github.com/Yanoona" class="translate-effect" target="_blank"><i class="fa fa-github"></i></a>
                        
                      
                        
                      
                        
                            <a href="https://blog.csdn.net/qq_39299449" class="translate-effect" target="_blank"><i class="fa fa-csdn"></i></a>
                        
                      
                        
                            <a href="https://www.jianshu.com/u/6d8fc7359702" class="translate-effect" target="_blank"><i class="fa fa-jianshu"></i></a>
                        
                      
                    </div>
                  </div>
                  <div class="clear"></div>
                  </section>
              </div>

            </div>
            <div class="clear"></div>
        </div>

        <p class="published border-effect">
          ©2020 共 12 篇文章
        </p>
        
        <a href="javascript:void(0)" id="back-to-top" class="epcl-button dark" style="display:none">
          <i class="fa fa-arrow"></i>
        </a>
    </footer>
    <div class="clear"></div>

<script type="text/javascript">
    jQuery(document).ready(function ($) {
        $("photos img").each(function () {
            var _a = $("<a></a>").attr("href", this.src);
            $(this).wrap("<div class='photo'></div>").wrap(_a);
        })
        $("figure img").each(function () {
            var _b = $("<a></a>").attr("href", this.src);
            $(this).wrap(_b);
        })
        isImgLoad(function () {
            var photos = document.querySelector('photos');
            if (photos) {
                waterfall(photos);
            }
            $(window).resize(function () {
                if (photos) {
                    waterfall(photos);
                }
            });
        });
        var t_img;
        var isLoad = true;

        function isImgLoad(callback) {
            $('photos img').each(function () {
                if (this.height === 0) {
                    isLoad = false;
                    return false;
                }
            });
            if (isLoad) {
                clearTimeout(t_img);
                callback();
            } else {
                isLoad = true;
                t_img = setTimeout(function () {
                    isImgLoad(callback);
                }, 500);
            }
        }
    });
</script>

</div>
<!-- end: #wrapper -->
</body>
</html>
